﻿CREATE PROC [dbo].[fillKonto] as
begin
------------------------------------
--00) Sichern der Originaltabelle
-------------------------------------
INSERT INTO dbo.[Version] values('AnzahlFiles',(SELECT count(distinct FileName) FROM dbo.[00_eingelesen]),getdate())
INSERT INTO dbo.[Version] values('00_eingelesen',(SELECT count(*) FROM dbo.[00_eingelesen]),getdate())

truncate table dbo.[01_bereinigt]
INSERT INTO dbo.[01_bereinigt] 
	SELECT	[Buchungstag]
			,[Wert]
			,[Verwendungszweck]
			,[Soll]
			,[Haben]
			,[Waehrung]
			,CASE WHEN FileName like '%5004635%' THEN '5004635' END AS KontoNr
	FROM dbo.[00_eingelesen]
	WHERE Buchungstag not in ('','Kontosaldo') AND Buchungstag is not null 
----------------------------
--01) import
----------------------------
UPDATE [01_bereinigt] SET Verwendungszweck = replace(Verwendungszweck,'"','')
UPDATE [01_bereinigt] SET Haben = replace(Haben,'.','') WHERE left(right(Haben,3),1) <> '.'
UPDATE [01_bereinigt] SET Soll = replace(Soll,'.','') WHERE left(right(SOLL,3),1) <> '.'
UPDATE [01_bereinigt] SET Haben = replace(Haben,',','.')
UPDATE [01_bereinigt] SET Soll = replace(Soll,',','.')
UPDATE [01_bereinigt] SET Haben = 0 WHERE Haben is null OR Haben = ''
UPDATE [01_bereinigt] SET Soll = 0 WHERE Soll IS null OR Soll = ''
-- 14.02.13 -> 2013-02-14
SET LANGUAGE german
update dbo.[01_bereinigt] SET Wert = cast(Wert as date) WHERE left(right(Wert,5),1) = '.'
update dbo.[01_bereinigt] SET Buchungstag = cast(Buchungstag as date) WHERE left(right(Buchungstag,5),1) = '.'

TRUNCATE TABLE dbo.[02_neue_Buchungen]
INSERT INTO dbo.[02_neue_Buchungen]
SELECT 
	cast(k.Buchungstag as date) as Buchungstag, 
	k.Verwendungszweck,
	( cast(k.Soll as decimal(7,2)) + cast(k.Haben as decimal(7,2))) as Betrag, 
	k.Wert as Wertdatum
	,cast(getdate() as datetime2(0)) as CreatedOn
	,0 as istDublette
	,KontoNr
FROM dbo.[01_bereinigt] k


----------------------------
--03) aussortierteDubletten
----------------------------
truncate table dbo.[03_aussortierteDubletten]

-- Dubletten markieren
UPDATE dbo.[02_neue_Buchungen]
SET istDublette = 1
WHERE dbo.fx_getGroupingString (Buchungstag,Verwendungszweck,Betrag) in
(	
	SELECT	dbo.fx_getGroupingString (Buchungstag,Verwendungszweck,Betrag) 
	FROM dbo.[02_neue_Buchungen]
	GROUP BY dbo.fx_getGroupingString (Buchungstag,Verwendungszweck,Betrag)
	having count(*) > 1
)
-- Singles retten
UPDATE dbo.[02_neue_Buchungen]
SET istDublette = 0
WHERE Id in
(	SELECT	min(Id)
	FROM dbo.[02_neue_Buchungen]
	GROUP BY dbo.fx_getGroupingString (Buchungstag,Verwendungszweck,Betrag)
	having count(*) > 1
)

-- Von den NichtDubletten diejenigen markieren, die es in der Persistenz schon gibt
update dbo.[02_neue_Buchungen] SET istDublette = 1
WHERE istDublette = 0 
AND dbo.fx_getGroupingString (Buchungstag,Verwendungszweck,Betrag) in 
(	SELECT	dbo.fx_getGroupingString (Buchungstag,Verwendungszweck,Betrag) 
	FROM dbo.[05_persistente_Buchungen]
)

-- Alle Dubletten ausser Singles löschen
INSERT INTO dbo.[03_aussortierteDubletten]
	SELECT [Buchungstag], [Verwendungszweck], [Betrag], [Wertdatum], [CreatedOn], KontoNr 
	FROM dbo.[02_neue_Buchungen] 
	WHERE istDublette = 1
DELETE FROM dbo.[02_neue_Buchungen] WHERE istDublette = 1

----------------------------------------------------------
--05) neue Buchungen eintragen
----------------------------------------------------------
INSERT INTO dbo.[05_persistente_Buchungen] ([Buchungstag],[Verwendungszweck],[Betrag],[Wertdatum],[CreatedOn],KontoNr)
	SELECT [Buchungstag],[Verwendungszweck],[Betrag],[Wertdatum],[CreatedOn],KontoNr
	FROM dbo.[02_neue_Buchungen]

-- Korrektur falls bei manuellen Buchungen vergessen
UPDATE dbo.[05_persistente_Buchungen] SET Wertdatum = Buchungstag WHERE Wertdatum IS null

----------------------------------------------------------
-- MatchID zuordnen. Getrennt nach Jahres / Quartalsoffsets
----------------------------------------------------------
-- '' Werte in den offset spalten auf NULL setzen, falls da manuell geändert wurde
-- Info: die offset Spalten sind als varchar modelliert, da sonst '' als 0 gewertet wird(!!)
UPDATE [ZweckMatch] SET JahresOffset = NULL WHERE JahresOffset = ''
UPDATE [ZweckMatch] SET QuartalsOffset = NULL WHERE QuartalsOffset = ''
-- Jetzt ursprüngliche Matches vorab löschen
UPDATE dbo.[05_persistente_Buchungen] SET ZweckMatchID = null

-- Kein offset angegeben
UPDATE k
SET k.ZweckMatchID = m.ZweckMatchID
FROM dbo.[05_persistente_Buchungen] k, [ZweckMatch] m 
WHERE k.Verwendungszweck LIKE '%'+m.MatchString+'%' 
AND m.JahresOffset IS NULL AND m.QuartalsOffset IS NULL
-- Offset ist in JAHREN angegeben
UPDATE k
SET k.ZweckMatchID = m.ZweckMatchID
	,k.Wertdatum = left(k.Buchungstag,4) + CAST(m.JahresOffset AS int)
FROM dbo.[05_persistente_Buchungen] k, [ZweckMatch] m 
WHERE k.Verwendungszweck LIKE '%'+m.MatchString+'%' AND m.JahresOffset IS NOT NULL 


-- Offset in Quartalen
UPDATE	k
	SET k.ZweckMatchID = m.ZweckMatchID
	,k.Wertdatum =
	-- Quartal ermitteln inkl. offset
	(SELECT [Quartal des Jahres] FROM dbo.[dim_Time] WHERE ID = CAST(m.QuartalsOffset AS int) +
		-- ID des Quartals ermitteln, welches dem Buchungstag zuzuordnen ist
		(SELECT min(ID) from dbo.[dim_Time] WHERE [dim_Time].[Quartal des Jahres] = t1.[Quartal des Jahres] AND dbo.[dim_Time].Quartal<>'' AND dbo.[dim_Time].Tag='')
	)
FROM 
	dbo.[05_persistente_Buchungen] k, 
	[ZweckMatch] m, [dim_Time] t1
WHERE k.Verwendungszweck LIKE '%'+m.MatchString+'%' AND m.QuartalsOffset IS NOT NULL
AND k.Buchungstag = t1.TimeKey

----------------------------------------------------------
--06a) Gegenbuchungen!!
----------------------------------------------------------
-- 1) löschen aller gegenbuchungen
truncate table dbo.[06_synthetische_Buchungen]
-- 2) kredit gegenbuchung
INSERT INTO dbo.[06_synthetische_Buchungen] (Buchungstag,Verwendungszweck,Betrag,Wertdatum,CreatedOn)
	SELECT Buchungstag,'XautogegenbuchungXkreditX',Betrag*-1, Wertdatum,getdate()
	FROM dbo.[05_persistente_Buchungen] WHERE Verwendungszweck LIKE '%gb$kredit%'
-- 3) cash gegenbuchung
INSERT INTO dbo.[06_synthetische_Buchungen] (Buchungstag,Verwendungszweck,Betrag,Wertdatum,CreatedOn)
	SELECT Buchungstag,'XautogegenbuchungXcashX',Betrag*-1, Wertdatum,getdate()
	FROM dbo.[05_persistente_Buchungen] WHERE Verwendungszweck LIKE '%gb$cash%'
-- 4) Synthis auch Matchen
UPDATE s
SET s.ZweckMatchID = m.ZweckMatchID
FROM dbo.[06_synthetische_Buchungen] s, [ZweckMatch] m 
WHERE s.Verwendungszweck LIKE '%'+m.MatchString+'%' 

----------------------------------------------------------
--06b) Sonderfall Rechnungsabgrenzung. Benötigt fertige MatchID
----------------------------------------------------------
-- gleitende Beträge für aktuelles Jahr ausbuchen ---------------
INSERT INTO dbo.[06_synthetische_Buchungen] (Buchungstag,Verwendungszweck,Betrag,Wertdatum,CreatedOn,ZweckMatchID)
(	SELECT Buchungstag,left(Verwendungszweck,32)+' XrechnungsabgrenzungX',dbo.fx_getShareOfNextYear(Buchungstag,Betrag) * -1, Wertdatum,getdate(),a.ZweckMatchID
	FROM dbo.[05_persistente_Buchungen] a JOIN [ZweckMatch] z On a.ZweckMatchID = z.ZweckMatchID
	WHERE z.istGleitend = '1'
	UNION ALL
	-- gleitende Beträge für kommendes Jahr einbuchen ---------------
	SELECT Buchungstag,left(Verwendungszweck,32)+' XrechnungsabgrenzungX',dbo.fx_getShareOfNextYear(Buchungstag,Betrag), Wertdatum+1,getdate(),a.ZweckMatchID
	FROM dbo.[05_persistente_Buchungen] a JOIN [ZweckMatch] z On a.ZweckMatchID = z.ZweckMatchID
	WHERE z.istGleitend = '1'
)

----------------------------------------------------------
-- Anpassungen MatchIds
----------------------------------------------------------
-- Buchungen nullen, auf die > 1 MatchString passen, oder doppelt vorkommen
update nm
set ZweckMatchID = null
from dbo.[nonMatches] nm
--Wenn ZweckId manuell vergeben, ZweckMatch vergessen
update dbo.[05_persistente_Buchungen] set ZweckMatchID = null where ZweckID_manuell is not null


----------------------------------------------------------
--Ausgabe
----------------------------------------------------------
--SELECT 'NeueBuchungen',[ID], [Buchungstag], [Verwendungszweck], [Betrag], [Wertdatum], [CreatedOn] FROM dbo.[02_neue_Buchungen]
--SELECT MehrfacheMatches, [MatchString], [ZweckMatchID], Id, [Verwendungszweck] FROM dbo.[mehrfacheMatches]
--SELECT NonMatches, Id, [Buchungstag], [Verwendungszweck], [Betrag], [Wertdatum], [CreatedOn], [ZweckMatchID], [ZweckID_manuell] FROM dbo.[nonMatches]
--SELECT 'OutOfTime' as Quelle,[Id], [Buchungstag], [Verwendungszweck], [Betrag], [Wertdatum], [CreatedOn], [ZweckMatchID], [ZweckID_manuell] FROM dbo.[05_persistente_Buchungen] k WHERE k.Wertdatum NOT IN (SELECT TimeKey from dbo.[dim_Time]) 

INSERT INTO dbo.[Version] values('02_neue_Buchungen',(SELECT count(*) FROM dbo.[02_neue_Buchungen]),getdate())
INSERT INTO dbo.[Version] values('99_final',(SELECT count(*) FROM dbo.[99_final]),getdate())
INSERT INTO dbo.[Version] values('nonMatches',(SELECT count(*) FROM dbo.nonMatches),getdate())
INSERT INTO dbo.[Version] values('mehrfacheMatches',(SELECT count(*) FROM dbo.mehrfacheMatches),getdate())

-- 1c) Offset in Quartalen
--Update k
--SET k.Wertdatum = tq2.Quartal_des_Jahres
--FROM 
--	dbo.[05_persistente_Buchungen] k
--	JOIN [Zweck_Match] m ON k.ZweckMatchID = m.ZweckMatchID AND m.QuartalsOffset IS NOT NULL
--	JOIN [Time] t ON k.Buchungstag = t.TimeKey
--	JOIN [Time_Quartale_Nummern] tq1 ON t.Quartal_des_Jahres = tq1.Quartal_des_Jahres
--	JOIN [Time_Quartale_Nummern] tq2 ON tq2.Quartal_Nummer = tq1.Quartal_Nummer + QuartalsOffset


end